package cn.lili.modules.promotion.mapper;

import cn.lili.modules.promotion.entity.dos.GreenUnitPrice;
import cn.lili.modules.promotion.entity.vos.GreenUnitPriceVO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author xiangqian
 * @version 1.0
 * @data 2023/12/20 13:44
 */
public interface GreenUnitPriceMapper extends BaseMapper<GreenUnitPrice> {

    /**
     * 按日查询
     */
    @Select("SELECT AVG(unit_price) AS average_price,DATE(unit_time) as t1, HOUR(unit_time) as t2,\n" +
            "       MIN(\n" +
            "           CASE\n" +
            "               WHEN MINUTE(unit_time) >= 30 THEN DATE_ADD(DATE_FORMAT(unit_time, '%Y-%m-%d %H:00:00'), INTERVAL 1 HOUR)\n" +
            "               ELSE DATE_FORMAT(unit_time, '%Y-%m-%d %H:00:00')\n" +
            "           END\n" +
            "       ) AS latest_time\n" +
            "FROM li_green_unit_price\n" +
            "WHERE DATE(unit_time) = CURDATE()\n" +
            "GROUP BY t1,t2 order by t2 asc;")
    List<GreenUnitPriceVO> queryDay();

    /**
     * 按周查询
     */
    @Select("SELECT AVG(unit_price) AS average_price,DATE(unit_time) as t1,\n" +
            "       MIN(\n" +
            "           CASE\n" +
            "               WHEN MINUTE(unit_time) >= 30 THEN DATE_ADD(DATE_FORMAT(unit_time, '%Y-%m-%d %H:00:00'), INTERVAL 1 HOUR)\n" +
            "               ELSE DATE_FORMAT(unit_time, '%Y-%m-%d %H:00:00')\n" +
            "           END\n" +
            "       ) AS latest_time\n" +
            "FROM li_green_unit_price\n" +
            "WHERE unit_time >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY)\n" +
            "AND unit_time < DATE_ADD(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 7 DAY)\n" +
            "GROUP BY t1 order by t1 asc")
    List<GreenUnitPriceVO> queryWeeks();


    /**
     * 按月查询
     */
    @Select("SELECT AVG(unit_price) AS average_price,DATE(unit_time) as t1,\n" +
            "       MIN(\n" +
            "           CASE\n" +
            "               WHEN MINUTE(unit_time) >= 30 THEN DATE_ADD(DATE_FORMAT(unit_time, '%Y-%m-%d %H:00:00'), INTERVAL 1 HOUR)\n" +
            "               ELSE DATE_FORMAT(unit_time, '%Y-%m-%d %H:00:00')\n" +
            "           END\n" +
            "       ) AS latest_time\n" +
            "FROM li_green_unit_price\n" +
            "WHERE unit_time >= DATE_FORMAT(CURDATE(), '%Y-%m-01')\n" +
            " AND unit_time < DATE_ADD(DATE_FORMAT(LAST_DAY(CURDATE()), '%Y-%m-%d'), INTERVAL 1 DAY)\n" +
            "GROUP BY t1 order by t1 asc")
    List<GreenUnitPriceVO> queryMonth();

}
